The present disclosure relates to computer software, and more specifically, to computer software to provide Boolean term conversion for NULL-tolerant disjunctive predicates in a database management system.
In relational databases and programming languages, NULL is a special value that signifies “unknown.” The result of comparing NULL with any value, including another NULL value, is unknown. The SQL standard defines an IS NULL predicate to qualify NULL values in relational databases.
Applications often need to search for both known values and NULL values, which requires the disjunction of an IS NULL predicate and another predicate for the known values. Disjunctive predicates are generally not eligible for single matching index access, and for matching index access require a multi-index access plan, due to the OR condition for the disjunctive predicates. For example, a query with two disjunctive predicates having two sets of OR conditions, when written in disjunctive normal form, may result in four predicates when each OR branch is distributed to each other OR branch. The complexity increases with more OR branches, which may reduce query efficiency because each OR branch requires a new index probe and intermediate row identifier (RID) or workfile storage in a multi-index access plan to union the results of each OR branch.